/* 
%excelcr(libname=japan, infile=pdev.sas7dbat, 
         exceldir=Y:\CLINICAL\TACHY\BIOSTAT\Duo\Projects\VR-IDE\stat\JAPAN\ExcelFiles\);
*/
%macro excelcr(libname=, infile=, indata=, exceldir=);
/*-------------------------------------------------------\
| Copy Right: Duo Zhou;                                  |
| Date: 9-28-2001;                                       |
| Purpose: List all datasets under the library reference |
\-------------------------------------------------------*/
%local num i ndsns dsn filename extension dataname directory;
%let libname=%sysfunc(dequote(&libname)); %let dataname =;
options noxwait noxsync; x 'Exit';
%if (%length(%trim(%left(&libname))) le 1) %then %do;
   %let infile=%sysfunc(dequote(&infile));
   %let indata=%sysfunc(dequote(&indata));
   %if (%quote(&infile) eq) and (%quote(&indata) ne) %then %do;
      %let infile=&indata;
   %end;
   %if (%substr(&infile, %length(&infile), 1) eq %str(\)) or (%substr(&infile, %length(&infile), 1) eq %str(/)) %then %do;
      libname _templib_ "&infile";
      %let libname=&_templib_;
   %end;
   %else %if (%substr(&infile, %length(&infile), 1) ne %str(\)) and (%substr(&infile, %length(&infile), 1) eq %str(/)) %then %do;
   	%let pathrc=%sysfunc(filename(pathrf,&infile));
   	%let psid=%sysfunc(DOPEN(&pathrf));
   	%if &psid %then %do;
         %let drc=%sysfunc(DCLOSE(&psid));
         libname _templib_ "&infile";
         %let libname=_templib_;
      %end;
      %else %if (%sysfunc(fileexist(&infile))) or (%sysfunc(fileexist(%trim(%left(&infile)).sas7bdat))) %then %do;
         %let dataname=%sysfunc(reverse(%scan(%sysfunc(reverse(&infile)), 1, %str(\/))));
         %let directory=%substr(&infile, 1, %eval(%length(&infile)-%length(&dataname)));
         %if (%index(%quote(&dataname), %str(.))) %then %do;
            %let extension=%sysfunc(reverse(%scan(%sysfunc(reverse(&infile)), 1, %str(.))));
            %let dataname=%substr(&dataname, 1, %eval(%eval(%length(&dataname)-%length(&extension))-1));
         %end;
         libname _templib_ "&directory";
         %let libname=_templib_;
      %end;
   %end;
%end;
%else %if (%length(%trim(%left(&libname))) gt 1) %then %do;
   %if (%quote(&infile) eq) and (%quote(&indata) ne) %then %do;
      %let infile=&indata;
   %end;
   %if (%length(%trim(%left(&infile))) ge 1) %then %do;
         %let dataname=%sysfunc(reverse(%scan(%sysfunc(reverse(&infile)), 1, %str(\/)))); ;
         %if (%index(%quote(&dataname), %str(.))) %then %do;
            %let extension=%sysfunc(reverse(%scan(%sysfunc(reverse(&infile)), 1, %str(.))));
            %let dataname=%substr(&dataname, 1, %eval(%eval(%length(&dataname)-%length(&extension))-1));
         %end;
   %end;
%end; 
%let exceldir=%sysfunc(dequote(&exceldir));
%if (%length(%trim(%left(&exceldir))) >1) %then %do;
   %if (%substr(&exceldir, %length(&exceldir), 1) ne %str(\)) %then %do;
      %let exceldir=&exceldir.\;
   %end;
%end;
proc datasets library=&libname memtype=data;
   contents out=work._temp1(keep=memname engine nobs varnum name sorted sortedby nodupkey noduprec) data=_all_ noprint;
run;
proc sort data=_temp1; by memname sorted sortedby; run;
%if %nobs(_temp1)>0 %then %do;
   %let datanames=;
	/*** Cocatenate data set names ***/
	proc sql noprint;
	  select distinct memname into :datanames separated by ' '
	  from _temp1
	  where name ne ' ' %if (%quote(&dataname) ne) %then %do; and upcase(memname) = %upcase("&dataname") %end;;
	quit; 
   %if (%quote(&datanames) ne ) %then %do;
   	/*** Create excel files ***/
   	%let ndsns=%words(&datanames);
      %put --> Note: Translating &ndsns data sets &datanames into excel sheets.;
   	ods listing close;
   	%do i=1 %to &ndsns;
   		%let filename=;
   		%let dsn&i=%qscan(&datanames, &i, %str( ));
   		%let filename=&&dsn&i...csv;
   		%put --> Note: Writing to the &i.th excel sheet: "&exceldir.&&dsn&i...csv".;
   		ods csv file="&exceldir.&filename";
   		proc print data=&libname..&&dsn&i label;
   		title "&&dsn&i";
   		run;
   		ods csv close;
   	%end;
   	ods listing;
   %end;
   %else %do;
      %put Note: Dataset "&dataname" doesn%str(%') exist;
   %end;
   proc datasets library=work nolist;
     delete _temp1;
   run;quit;
%end;
%else %put There is no datasets in the library "&libname".;
%mend excelcr;